/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO.MySQL;

import Biblioteca.Library;
import ClassesUnicas.Profissional;
import DAO.DAOProfissional;
import java.net.InetAddress;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;

/**
 * DAOProfissionalMySQL
 *
 * Versao 1.0
 *
 * Data Criação 13/03/2014
 *
 * @author Igor
 */
public class DAOProfissionalMySQL extends DAOConexaoMySQL implements DAOProfissional {

    @Override
    public String sqlPesquisaPadrao() {
        return "select\n"
                + "a.ID_PROFISSIONAL,\n"
                + "a.NOME,\n"
                + "a.EMAIL,\n"
                + "AES_DECRYPT(a.SENHA, 'SENHA') as SENHA,\n"
                + "a.CPF,\n"
                + "a.INSC_CONSELHO_UF,\n"
                + "a.INSC_CONSELHO_NUMERO,\n"
                + "a.ID_NIVEL_ACESSO,\n"
                + "a.ID_PROFISSAO\n"
                + "from profissional a\n";
    }

    @Override
    public long autenticarProfissional(String email, String senha, String software) throws Exception {
        long vReturn = -1; //Representa que nao achou o profissional

        CallableStatement cs;
        cs = this.con.getCon().prepareCall("{ ? = call AUTENTICAR_PROFISSIONAL(?,?,?,?)}");

        cs.registerOutParameter(1, Types.NUMERIC);
        cs.setString(2, email);
        cs.setString(3, senha);
        cs.setString(4, software);
        cs.setString(5, InetAddress.getLocalHost().getHostAddress());
        cs.execute();

        vReturn = cs.getLong(1);
        this.con.getStm().executeBatch();

        return vReturn;
    }

    @Override
    public Profissional buscaProfissional(String email, String senha) throws Exception {
        return buscaProfissional(String.valueOf(autenticarProfissional(email, senha, Library.getConfigTREF("TREF_Sistema"))));
    }

    @Override
    public Profissional buscaProfissional(String idProfissional) throws Exception {
        ResultSet result = null;
        Profissional profissional = null;

        result = this.select.executeQuery(sqlPesquisaPadrao()
                + "where a.ID_PROFISSIONAL = " + idProfissional);

        while (result.next()) {
            profissional = new Profissional();

            profissional.setIdProfissional(result.getLong("ID_PROFISSIONAL"));
            profissional.setNome(result.getString("NOME"));
            profissional.setEmail(result.getString("EMAIL"));
            profissional.setSenha(result.getString("SENHA"));
            profissional.setCpf(result.getString("CPF"));
            profissional.setInsc_conselho_uf(result.getString("INSC_CONSELHO_UF"));
            profissional.setInsc_conselho_numero(result.getString("INSC_CONSELHO_NUMERO"));
            profissional.setIdNivelAcesso(result.getLong("ID_NIVEL_ACESSO"));
            profissional.setIdProfissao(result.getLong("ID_PROFISSAO"));

        }

        return profissional;
    }

    @Override
    public ArrayList<Profissional> buscaProfissionais(String idProfissional, String nome) throws Exception {
        ResultSet result = null;
        ArrayList<Profissional> profissionais = new ArrayList<Profissional>();
        String clausulaWhere = "";

        //Cria clausula where
        if (idProfissional != null) {
            if (clausulaWhere.isEmpty()) {
                clausulaWhere = "where ";
            } else {
                clausulaWhere = clausulaWhere + " and ";
            }
            clausulaWhere = clausulaWhere + " a.ID_PROFISSIONAL = " + idProfissional;
        }

        if (nome != null) {
            if (clausulaWhere.isEmpty()) {
                clausulaWhere = "where ";
            } else {
                clausulaWhere = clausulaWhere + " and ";
            }
            clausulaWhere = clausulaWhere + " a.NOME like '%" + nome + "%'";
        }

        result = this.select.executeQuery(sqlPesquisaPadrao()
                + clausulaWhere);

        while (result.next()) {
            Profissional profissional = new Profissional();

            profissional.setIdProfissional(result.getLong("ID_PROFISSIONAL"));
            profissional.setNome(result.getString("NOME"));
            profissional.setEmail(result.getString("EMAIL"));
            profissional.setSenha(result.getString("SENHA"));
            profissional.setCpf(result.getString("CPF"));
            profissional.setInsc_conselho_uf(result.getString("INSC_CONSELHO_UF"));
            profissional.setInsc_conselho_numero(result.getString("INSC_CONSELHO_NUMERO"));
            profissional.setIdNivelAcesso(result.getLong("ID_NIVEL_ACESSO"));
            profissional.setIdProfissao(result.getLong("ID_PROFISSAO"));
            profissionais.add(profissional);

        }

        return profissionais;
    }

    @Override
    public String gravaProfissional(Profissional profissional) throws Exception {
        String vReturn = ""; //Representa que nao achou o profissional

        CallableStatement cs;
        cs = this.con.getCon().prepareCall("{ ? = call GRAVA_PROFISSIONAL(?,?,?,?,?,?,?,?,?)}");

        cs.registerOutParameter(1, Types.NUMERIC);
        cs.setLong(2, profissional.getIdProfissional());
        cs.setString(3, profissional.getNome());
        cs.setString(4, profissional.getEmail());
        cs.setString(5, profissional.getSenha());
        cs.setString(6, profissional.getCpf());
        cs.setString(7, profissional.getInsc_conselho_uf());
        cs.setString(8, profissional.getInsc_conselho_numero());

        String idNivelAcesso = null;
        if (profissional.getIdNivelAcesso() != 0) {
            idNivelAcesso = String.valueOf(profissional.getIdNivelAcesso());
        }
        cs.setString(9, idNivelAcesso);

        String idProfissao = null;
        if (profissional.getIdProfissao() != 0) {
            idProfissao = String.valueOf(profissional.getIdProfissao());
        }
        cs.setString(10, idProfissao);
        cs.execute();

        vReturn = cs.getString(1);
        this.con.getStm().executeBatch();

        return vReturn;
    }
}
